package com.litidea.dao.impl;

import static com.litidea.commons.dict.StaticDics.GENDER_MAP;
import static com.litidea.commons.keys.SystemKeys.BASE_DEL;
import static com.litidea.commons.keys.SystemKeys.DEL_MOVE_CHILDS;
import static com.litidea.commons.keys.SystemKeys.MOVE_AFTER;
import static com.litidea.commons.keys.SystemKeys.MOVE_BEFORE;
import static com.litidea.commons.keys.SystemKeys.MOVE_TO_CHILD;
import static com.litidea.commons.tools.ConvertPassword.getMyPassword;
import static com.litidea.dao.DAOKeys.TABLE_START;
import static com.litidea.dao.sqls.SystemDAOSqls.ADD_NEW_ROLE;
import static com.litidea.dao.sqls.SystemDAOSqls.ADD_ROLE_MENUS;
import static com.litidea.dao.sqls.SystemDAOSqls.ADD_USER;
import static com.litidea.dao.sqls.SystemDAOSqls.ADD_USER_LOGIN;
import static com.litidea.dao.sqls.SystemDAOSqls.ADD_USER_ROLE;
import static com.litidea.dao.sqls.SystemDAOSqls.BASE_DEPT_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.BASE_MENU_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.CHG_ROLE_USERCOUNT;
import static com.litidea.dao.sqls.SystemDAOSqls.CHG_USER;
import static com.litidea.dao.sqls.SystemDAOSqls.CHG_USER_PASS;
import static com.litidea.dao.sqls.SystemDAOSqls.DELETE_DEPT_CASCADE;
import static com.litidea.dao.sqls.SystemDAOSqls.DELETE_DEPT_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.DEL_ROLE;
import static com.litidea.dao.sqls.SystemDAOSqls.DEL_ROLE_MENUS;
import static com.litidea.dao.sqls.SystemDAOSqls.DEL_USER;
import static com.litidea.dao.sqls.SystemDAOSqls.DEL_USER_ROLES;
import static com.litidea.dao.sqls.SystemDAOSqls.DEPT_TBALE;
import static com.litidea.dao.sqls.SystemDAOSqls.EDIT_ROLE;
import static com.litidea.dao.sqls.SystemDAOSqls.GET_MENU_INFO_BY_WF_KEY;
import static com.litidea.dao.sqls.SystemDAOSqls.GET_PRIV_FIRST_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.INSERT_DEPT_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.MOVE_TO_PARENT;
import static com.litidea.dao.sqls.SystemDAOSqls.ROLE_MENUS;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_ROLES;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_ROLES_BY_USER;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_ROLES_NOT_USER;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_USER_BASE;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_USER_BY_ID;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_USER_BY_LOGIN;
import static com.litidea.dao.sqls.SystemDAOSqls.SEARCH_USER_BY_ROLES;
import static com.litidea.dao.sqls.SystemDAOSqls.SET_DEPT_MGR_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.UPDATE_CHILD_ORDER;
import static com.litidea.dao.sqls.SystemDAOSqls.UPDATE_DEPT_SQL;
import static com.litidea.dao.sqls.SystemDAOSqls.UPDATE_ROLE_MENUS;
import static com.litidea.dao.sqls.SystemDAOSqls.UPDATE_USER_LOGIN_INFO;
import static com.litidea.dao.sqls.SystemDAOSqls.USER_LOGIN_OUT;
import static com.litidea.dao.sqls.SystemDAOSqls.USER_TABLE;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.RowMapper;

import com.litidea.commons.config.BaseConfig;
import com.litidea.commons.dict.DynamicDicts;
import com.litidea.commons.utils.CollectionUtils;
import com.litidea.commons.utils.JSONUtils;
import com.litidea.dao.SystemDAO;
import com.litidea.dao.abs.SystemDAOAbs;
import com.litidea.dataobjects.DepartmentDO;
import com.litidea.dataobjects.MenuNodeDO;
import com.litidea.dataobjects.RoleDO;
import com.litidea.dataobjects.SqlExecuteDO;
import com.litidea.dataobjects.UserDO;

@SuppressWarnings("unchecked")
public class SystemDAOImpl extends SystemDAOAbs implements SystemDAO {

	public void init() {
		DynamicDicts.initCache(jdbcTemplate);
		BaseConfig.initCache(jdbcTemplate);
	}

	/**
	 * 获取一级菜单列表
	 * 
	 * @return
	 */
	public List<MenuNodeDO> getMenuNodeDOs() {
		String sql = BASE_MENU_SQL + "where t.level_id=1 order by order_id";
		return jdbcTemplate.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneMenu(rs);
			}
		});
	}

	/**
	 * 获取用户的权限一级菜单
	 * 
	 * @return
	 */
	public List<MenuNodeDO> getFirstPrivMenus(Integer userId) {
		return jdbcTemplate.query(GET_PRIV_FIRST_SQL, new Object[] { userId },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneMenu(rs);
					}
				});
	}

	/**
	 * 获取菜单DO
	 * 
	 * @param wfKey
	 * @return
	 */
	public MenuNodeDO getMenuByWfKey(String wfKey) {
		List<MenuNodeDO> list = jdbcTemplate.query(GET_MENU_INFO_BY_WF_KEY,
				new Object[] { wfKey }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneMenu(rs);
					}
				});
		return CollectionUtils.getFirstValue(list);
	}

	/**
	 * 获取所有子节点列表
	 * 
	 * @param allParentId
	 * @return
	 */
	public List<MenuNodeDO> getMenuChilds(String allParentId, Integer userId) {
		final StringBuilder buf = getUserMenuIds(allParentId, userId);
		final String sql = BASE_MENU_SQL
				+ "where t.all_parent_id like ? "
				+ "and (select FIND_IN_SET(id, col1) from (select ? as col1) as tab1) order by level_id,order_id";
		return jdbcTemplate.query(sql, new Object[] { allParentId + "%", buf },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneMenu(rs);
					}
				});
	}

	/**
	 * 获取用户当前节点的所有子节点
	 * 
	 * @param allParentId
	 * @param userId
	 * @return
	 */
	private StringBuilder getUserMenuIds(String allParentId, Integer userId) {
		final String userAllParentSql = "select t3.all_parent_id from "
				+ TABLE_START
				+ "user_roles t1, "
				+ TABLE_START
				+ "role_menus t2, "
				+ TABLE_START
				+ "menus t3 "
				+ "where t1.role_id = t2.role_id and t2.menu_id = t3.id and t1.user_id = ? and t3.all_parent_id like ?";
		final StringBuilder buf = new StringBuilder(128);
		final Map<String, String> ids = new HashMap<String, String>(32);
		jdbcTemplate.query(userAllParentSql, new Object[] { userId,
				allParentId + "%" }, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				String allParent = rs.getString(1);
				String[] allIds = getAllparentIds(allParent);
				for (String allId : allIds) {
					if (!ids.containsKey(allId)) {
						ids.put(allId, null);
						buf.append(allId).append(",");
					}
				}
				return null;
			}
		});
		return buf;
	}

	/**
	 * 获取所有的菜单列表
	 * 
	 * @return
	 */
	public List<MenuNodeDO> getAllMenus() {
		final String sql = BASE_MENU_SQL + " order by level_id,order_id";
		return jdbcTemplate.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneMenu(rs);
			}
		});
	}

	/**
	 * 通过部门编号获取部门信息
	 * 
	 * @param id
	 * @return
	 */
	public DepartmentDO getDepartMentById(String id) {
		final String sql = BASE_DEPT_SQL + " where id=?";
		List<DepartmentDO> list = jdbcTemplate.query(sql, new Object[] { id },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneDept(rs);
					}
				});
		return (DepartmentDO) CollectionUtils.getFirstValue(list);
	}

	/**
	 * 获取所有的组织架构信息
	 * 
	 * @return
	 */
	public List<DepartmentDO> getAllDepartMents() {
		final String sql = BASE_DEPT_SQL + " order by level_id,order_id";
		return jdbcTemplate.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneDept(rs);
			}
		});
	}

	/**
	 * 获取部门列表，排序方法按照all_parent_id
	 * 
	 * @return
	 */
	public List<DepartmentDO> getAllDepartmentByParent() {
		final String sql = BASE_DEPT_SQL + " order by all_parent_id";
		return jdbcTemplate.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneDept(rs);
			}
		});
	}

	/**
	 * 只选择普通部门和船厂
	 * 
	 * @return
	 */
	public List<DepartmentDO> getDepartmentToHandout() {
		final String sql = BASE_DEPT_SQL
				+ " where dept_type in('BASE' , 'SHIP') order by all_parent_id";
		return jdbcTemplate.query(sql, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneDept(rs);
			}
		});
	}

	/**
	 * 添加子部门信息
	 * 
	 * @param map
	 * @return
	 */
	public String saveChildDepartment(Map<String, String> map) {
		String parentId = map.get("id");
		DepartmentDO nowDept = getDepartMentById(parentId);
		Integer deptId = getNextDeptId();
		String allParentId = nowDept.getAllParentId() + "{" + deptId + "}";
		String deptFullName = nowDept.getDeptFullName() + "-"
				+ map.get("deptName");

		String deptResponsible = "";
		if (map.get("deptResponsible") != null)
			deptResponsible = map.get("deptResponsible");

		String deptDesc = "";

		if (map.get("deptDesc") != null)
			deptDesc = map.get("deptDesc");
		
		String deptType = "";
		if (map.get("deptType") != null)
			deptType = map.get("deptType");

		jdbcTemplate.update(INSERT_DEPT_SQL, new Object[] { deptId,
				map.get("deptName"), parentId, allParentId, deptFullName,
				nowDept.getLevelId() + 1, getChildMaxOrderId(parentId) + 1,
				deptResponsible, deptDesc, deptType });
		return allParentId;
	}

	/**
	 * 添加同级别部门
	 * 
	 * @param map
	 * @return
	 */
	public String addSiblingDepartment(Map<String, String> map) {
		String parentId = map.get("parentId");
		Integer deptId = getNextDeptId();
		String allParentId = null;
		String deptFullName = null;
		Integer level = null;
		if (StringUtils.isEmpty(parentId) || "0".equals(parentId)) {
			allParentId = "{" + deptId + "}";
			deptFullName = map.get("deptName");
			level = 1;
		} else {
			DepartmentDO nowDept = getDepartMentById(parentId);
			allParentId = nowDept.getAllParentId() + "{" + deptId + "}";
			deptFullName = nowDept.getDeptFullName() + "-"
					+ map.get("deptName");
			level = nowDept.getLevelId() + 1;
		}
		jdbcTemplate.update(INSERT_DEPT_SQL, new Object[] { deptId,
				map.get("deptName"), parentId, allParentId, deptFullName,
				level, getChildMaxOrderId(parentId) + 1,
				map.get("deptResponsible"), map.get("deptDesc"), map.get("deptType") });
		return allParentId;
	}

	/**
	 * 修改部门信息
	 * 
	 * @param map
	 * @return
	 */
	public String editDepartMent(Map<String, String> map) {
		String id = map.get("id");
		String deptName = map.get("deptName");
		DepartmentDO nowDept = getDepartMentById(id);
		String fullName = null;
		if (nowDept.getLevelId() == 1) {
			fullName = deptName;
		} else {
			fullName = nowDept.getDeptFullName();
			fullName = fullName.substring(0, fullName.lastIndexOf("-") + 1)
					+ deptName;
		}
		jdbcTemplate.update(UPDATE_DEPT_SQL, new Object[] { deptName, fullName,
				map.get("deptResponsible"),
				map.get("deptDesc"), map.get("deptType"), id });
		return nowDept.getAllParentId();
	}

	/**
	 * 删除一个部门
	 * 
	 * @param map
	 * @return
	 */
	public String delDeparentMent(Map<String, String> map) {
		String id = map.get("id");
		int model = Integer.valueOf(map.get("model"));
		DepartmentDO nowDept = getDepartMentById(id);
		String allParentId = nowDept.getAllParentId();

		// 判断如果该部门属于跟级别部门则不允许删除
		if (nowDept.getLevelId() == 1) {
			throw new RuntimeException("根部门不允许删除");
		}

		if (model == BASE_DEL || model == DEL_MOVE_CHILDS) {// 删除掉当前节点
			jdbcTemplate.update(DELETE_DEPT_SQL, new Object[] { id });
			if (model == 2) {// 将子部门移动到父亲部门
				String parentId = (nowDept.getParentId() == null ? "0"
						: nowDept.getParentId().toString());
				jdbcTemplate.update(MOVE_TO_PARENT, new Object[] {
						"{" + id + "}", "-" + nowDept.getDeptName(),
						allParentId + "%" });
				Integer maxOrder = getChildMaxOrderId(parentId);
				jdbcTemplate.update(UPDATE_CHILD_ORDER, new Object[] {
						maxOrder, parentId, id });
			}
		} else {// 级联删除
			jdbcTemplate.update(DELETE_DEPT_CASCADE,
					new Object[] { allParentId });
		}
		if (nowDept.getLevelId() == 1)
			return allParentId;
		else {
			return allParentId.substring(0, allParentId.lastIndexOf("{"));
		}
	}

	/**
	 * 移动部门到指定位置
	 * 
	 * @param params
	 * @return
	 */
	public String moveDepartMent(Map<String, String> params) {
		String id = params.get("id");
		String compId = params.get("compId");
		DepartmentDO nowDept = getDepartMentById(id);
		DepartmentDO compDept = getDepartMentById(compId);
		int moveType = Integer.valueOf(params.get("moveType"));
		switch (moveType) {
		case MOVE_BEFORE:
			return moveToBefore(nowDept, compDept);
		case MOVE_AFTER:
			return moveToAfter(nowDept, compDept);
		case MOVE_TO_CHILD:
			return moveToChild(nowDept, compDept);
		}
		return null;
	}

	@Override
	public String setDeptMgr(Map<String, String> params) {
		// TODO Auto-generated method stub
		String id = params.get("id");
		String deptMgr = params.get("deptMgr");
		
		jdbcTemplate.update(SET_DEPT_MGR_SQL, new Object[] { deptMgr, id});
		return "设置部门领导成功";
	}

	/**
	 * 添加一个角色
	 * 
	 * @param params
	 */
	public void addNewRole(Map<String, String> params) {
		Integer maxOrder = this.getMaxRoleOrder();
		Integer maxId = this.getMaxRoleId();
		jdbcTemplate.update(ADD_NEW_ROLE, new Object[] { maxId,
				params.get("name"), params.get("responsible"), maxOrder + 1,
				params.get("roleKey") });
	}

	/**
	 * 获取角色列表
	 * 
	 * @param ids
	 * @return
	 */
	public List<RoleDO> getRolesByIds(String ids) {
		return jdbcTemplate.query(SEARCH_ROLES, new Object[] { ids },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneRole(rs);
					}
				});
	}

	/**
	 * 编辑角色信息
	 * 
	 * @param params
	 */
	public void editRole(Map<String, String> params) {
		jdbcTemplate.update(EDIT_ROLE, new Object[] { params.get("name"),
				params.get("responsible"), params.get("roleKey"),
				params.get("id") });
	}

	/**
	 * 删除一个Role信息
	 * 
	 * @param params
	 */
	public void delRole(Map<String, String> params) {
		/**
		 * 这里应当包含对role的删除校验，目前这部分待人员管理完成后继续完成
		 */
		jdbcTemplate.update(DEL_ROLE, new Object[] { params.get("id") });
	}

	/**
	 * 获取role的分页列表
	 * 
	 * @param params
	 * @return
	 */
	public JSONObject getRolePageList(Map<String, String> params) {
		return JSONUtils.getPageJSON(this.getRoles(params), this
				.getRoleCount(params));
	}

	/**
	 * 获取Role对应的菜单列表
	 * 
	 * @param roleId
	 * @return
	 */
	public Map<String, Integer> getRoleMenus(String roleId) {
		final Map<String, Integer> map = new HashMap<String, Integer>();
		jdbcTemplate.query(ROLE_MENUS, new Object[] { roleId },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						String allParentId = rs.getString("all_parent_id");
						fillAllParentId(map, allParentId);
						return null;
					}
				});
		return map;
	}

	/**
	 * 保存Role信息
	 * 
	 * @param menus
	 * @param roleId
	 */
	public void saveRoleMenus(String[] menus, String roleId) {
		SqlExecuteDO delExecute = new SqlExecuteDO(DEL_ROLE_MENUS, 1);
		SqlExecuteDO addExecute = new SqlExecuteDO(ADD_ROLE_MENUS,
				menus == null ? 0 : menus.length);
		SqlExecuteDO updateRoleExecute = new SqlExecuteDO(UPDATE_ROLE_MENUS, 1);

		delExecute.addParams(new Object[] { roleId });
		int newRoles = 0;
		if (menus != null) {
			for (String menu : menus) {
				if (StringUtils.isNotEmpty(menu)) {
					addExecute.addParams(new Object[] { roleId, menu });
					newRoles++;
				}
			}
		}
		updateRoleExecute.addParams(new Object[] { newRoles, roleId });
		this.executeBatchSql(delExecute, addExecute, updateRoleExecute);
	}

	/**
	 * 获取user的分页列表
	 * 
	 * @param params
	 * @return
	 */
	public JSONObject getUserPageList(Map<String, String> params) {
		return JSONUtils.getPageJSON(this.getUsers(params), this
				.getUserCount(params));
	}
	
	/**
	 * 通过部门ID获取用户时没有部门名称
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private Object getOneUserDO_1(ResultSet rs) throws SQLException {
		Integer gender = rs.getInt("gender");
		String genderStr = GENDER_MAP.get(gender);
		return new UserDO(
				rs.getInt("id") ,rs.getString("name"),rs.getString("id_card"),
				rs.getString("login_name") , rs.getString("email"),rs.getString("tel"),
				rs.getString("tel2"),rs.getString("user_content"),
				rs.getString("certificate"),rs.getString("user_desc"),gender,
				genderStr,rs.getString("birthDay"),rs.getInt("dept_id") , 
				rs.getString("mobile"),
				rs.getString("before_login") , rs.getString("last_login"),
				rs.getString("before_login_ip") , rs.getString("last_login_ip")
		);
	}
	
	public List<UserDO> getUserByDept(Integer deptId) {
		try{
		StringBuilder sqlbuf = new StringBuilder(1024).append(SEARCH_USER_BASE);
		sqlbuf.append("from " + USER_TABLE + " a, " + DEPT_TBALE +" b where a.dept_id=b.id and b.all_parent_id like (select CONCAT(c.all_parent_id,'%') from " + DEPT_TBALE +" c where c.id=?)");
		return jdbcTemplate.query(sqlbuf.toString() , new Object[]{deptId} , new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return getOneUserDO_1(rs);
			}
		});}catch(Exception e){
			e.printStackTrace();
		}return null;
	}

	/**
	 * 根据用户名获取用户信息
	 * 
	 * @return
	 */
	public UserDO getUserByLoginName(String loginName) {
		List<UserDO> users = jdbcTemplate.query(SEARCH_USER_BY_LOGIN,
				new Object[] { loginName }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneUserAndPassDO(rs);
					}
				});
		return (UserDO) CollectionUtils.getFirstValue(users);
	}

	/**
	 * 根据用户名获取用户信息
	 * 
	 * @return
	 */
	public UserDO getUserByUserId(String userId) {
		List<UserDO> users = jdbcTemplate.query(SEARCH_USER_BY_ID,
				new Object[] { userId }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneUserDO(rs);
					}
				});
		return (UserDO) CollectionUtils.getFirstValue(users);
	}

	/**
	 * 通过角色ID获取用户列表
	 * 
	 * @param roles
	 * @return
	 */
	public List<UserDO> getUsersByRoles(String roles) {
		return jdbcTemplate.query(SEARCH_USER_BY_ROLES, new Object[] { roles },
				new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneUserDO(rs);
					}
				});
	}

	/**
	 * 添加一个用户
	 * 
	 * @param params
	 * 
	 */
	public void addNewUser(Map<String, String> params) {
		Integer maxOrder = this.getMaxUserOrder();
		String loginName = params.get("loginName");
		checkUserExists(loginName);
		jdbcTemplate.update(ADD_USER, new Object[] { getNextUserId(),
				params.get("name"), params.get("idCard"), loginName,
				getMyPassword(params.get("password")), params.get("email"),
				params.get("tel"), params.get("tel2"), params.get("mobile"),
				params.get("userContent"), params.get("certificate"),
				params.get("userDesc"), params.get("gender"),
				params.get("birthDay"), params.get("deptId"), maxOrder });
	}

	/**
	 * 修改一个用户
	 * 
	 * @param params
	 */
	public void editUser(Map<String, String> params) {
		String id = params.get("id");
		UserDO userDO = this.getUserById(id);
		String loginName = params.get("loginName");
		if (!userDO.getLoginName().equals(loginName)) {
			checkUserExists(loginName);// 用户名和原来不一致，则要开始检查是否和数据库内部冲突
		}
		jdbcTemplate.update(CHG_USER, new Object[] { params.get("name"),
				params.get("idCard"), loginName, params.get("email"),
				params.get("tel"), params.get("tel2"), params.get("mobile"),
				params.get("userContent"), params.get("certificate"),
				params.get("userDesc"), params.get("gender"),
				params.get("birthDay"), params.get("deptId"), id });
	}

	/**
	 * 修改用户的密码
	 * 
	 * @param userDO
	 */
	public void chgUserPassWord(UserDO userDO) {
		this.jdbcTemplate.update(CHG_USER_PASS, new Object[] {
				userDO.getUserPass(), userDO.getId() });
	}

	/**
	 * 删除用户信息
	 * 
	 * @param params
	 */
	public void delUser(Map<String, String> params) {
		String userId = params.get("id");
		jdbcTemplate.update(DEL_USER, new Object[] { userId });
		jdbcTemplate.update(DEL_USER_ROLES, new Object[] { userId });
	}

	/**
	 * 获取用户已经存在的角色信息
	 * 
	 * @return
	 */
	public List<RoleDO> getUserRoles(String userId) {
		return jdbcTemplate.query(SEARCH_ROLES_BY_USER,
				new Object[] { userId }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneRole(rs);
					}
				});
	}

	/**
	 * 获取不是用户的角色信息
	 * 
	 * @param userId
	 * @return
	 */
	public List<RoleDO> getNotUserRoles(String userId, String name) {
		List<Object> params = new ArrayList<Object>(2);
		StringBuilder buf = new StringBuilder(
				SEARCH_ROLES_NOT_USER.length() + 64)
				.append(SEARCH_ROLES_NOT_USER);
		this.appendSql(buf, name, " and name like ?", params, true);
		return jdbcTemplate.query(SEARCH_ROLES_NOT_USER,
				new Object[] { userId }, new RowMapper() {
					public Object mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						return getOneRole(rs);
					}
				});
	}

	/**
	 * 保存用户的角色信息
	 * 
	 * @param userId
	 * @param roles
	 */
	public void saveUserRoles(String userId, String[] roles) {
		SqlExecuteDO delExecute = new SqlExecuteDO(DEL_USER_ROLES, 1);
		SqlExecuteDO addExecute = new SqlExecuteDO(ADD_USER_ROLE,
				roles == null ? 0 : roles.length);
		SqlExecuteDO updateUserExecute = new SqlExecuteDO(CHG_ROLE_USERCOUNT, 1);
		delExecute.addParams(new Object[] { userId });
		if (roles != null) {
			StringBuilder buf = new StringBuilder(256);
			for (String role : roles) {
				if (StringUtils.isNotEmpty(role)) {
					addExecute.addParams(new Object[] { userId, role });
					buf.append(role).append(",");
				}
			}
			updateUserExecute.addParams(new Object[] { buf.toString() });
		}
		this.executeBatchSql(delExecute, addExecute, updateUserExecute);
	}

	/**
	 * 添加一个登陆日志
	 * 
	 * @param userId
	 * @return
	 */
	public Integer addLogin(Integer userId, String ip) {
		Integer loginId = this.jdbcTemplate.update(ADD_USER_LOGIN,
				new Object[] { userId });
		this.jdbcTemplate.update(UPDATE_USER_LOGIN_INFO, new Object[] { ip,
				userId });
		return loginId;
	}

	/**
	 * 登出时间
	 * 
	 * @param loginId
	 */
	public void loginOut(Integer loginId, Integer type) {
		this.jdbcTemplate
				.update(USER_LOGIN_OUT, new Object[] { type, loginId });
	}
}
